package com.yj.agent.provider;

import com.yj.agent.evt.QueryAgentUserEvt;
import com.yj.agent.evt.QueryWithDrawEvt;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.jdbc.SQL;

public class AgentUserProvider {

    private final String TBL = "t_agent_user";

    private final String SUB_TBL = "t_agent_user a,t_agent_user b";

    public String queryByGameId(String gameId) {
        SQL sql = new SQL().SELECT("*")
                .FROM("t_agent_user");
        sql.WHERE("gameId='" + gameId + "'");
        return sql.toString();
    }

    public String sumProfit(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("sum(amount)")
                .FROM("t_agent_user_rate");
        if (StringUtils.isBlank(evt.getGameId())) {
            sql.WHERE("gameId = '" + evt.getGameId() + "'");
        }
        if (StringUtils.isBlank(evt.getAgentId())) {
            sql.WHERE("agentGameId = '" + evt.getAgentId() + "'");
        }
        return sql.toString();
    }

    public String rechargeTop(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("a.gameId,b.account,b.userName,sum(a.amount) as amount")
                .FROM("t_agent_user_recharge a")
                .LEFT_OUTER_JOIN("t_agent_user b on a.gameId = b.gameId")
                .WHERE("a.gameId in (" + evt.getInConditon() + ")")
                .GROUP_BY("a.gameId,b.account")
                .ORDER_BY("amount desc");
        return sql.toString();
    }

    public String rechargeCount(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("ifnull(sum(amount),0) as rechargeCount")
                .FROM("t_agent_user_recharge")
                .WHERE("gameId in (" + evt.getInConditon() + ")");
        if (evt.isNow()) {
            sql.WHERE("rechargeTime >= UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))*1000");
        }
        return sql.toString();
    }

    public String teamCount(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("count(1)").FROM("t_agent_user");
        if (evt.getAgentLevel() != null) {
            sql.WHERE("agentLevel = " + evt.getAgentLevel());
        }
        if (evt.getNotEqAgentLevel() != null) {
            sql.WHERE("agentLevel <> " + evt.getNotEqAgentLevel());
        }
        if (StringUtils.isNotBlank(evt.getInConditon())) {
            sql.WHERE("gameId in (" + evt.getInConditon() + ")");
        }
        sql.WHERE("status = 'E'");
        return sql.toString();
    }


    public String querySubCount(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("count(1)").FROM(SUB_TBL);
        sql = buildSub(evt, sql);
        return sql.toString();
    }

    public String queryDetail(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("*,\n" +
                "\t(select sum(amount) as totalRate from t_agent_user_rate a where a.agentGameId = b.gameId) as totalRate")
                .FROM("t_agent_user b");
        if (StringUtils.isNotBlank(evt.getAccount())) {
            sql.WHERE("account = '" + evt.getAccount() + "'");
        }
        if (StringUtils.isNotBlank(evt.getUserName())) {
            sql.WHERE("userName = '" + evt.getUserNo() + "'");
        }
        return sql.toString();
    }

    public String querySubByParam(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("b.*,a.userName as parentUserName,\n" +
                "\t(select sum(amount) as totalRate from t_agent_user_rate a where a.gameId = b.gameId) as totalRate,\n" +
                "  (select sum(amount) as totalRate from t_agent_user_rate a where a.agentGameId = b.gameId) as totalOffer")
                .FROM(SUB_TBL);
        sql = buildSub(evt, sql);
        return sql.toString();
    }

    private SQL buildSub(QueryAgentUserEvt evt, SQL sql) {
        sql.WHERE("a.gameId = b.parentGameId");
        if (evt.getId() != null) {
            sql.WHERE("b.id = " + evt.getId());
        } else {

            if (StringUtils.isNotBlank(evt.getParentUserNo())) {
                sql.WHERE("a.userName like '%" + evt.getUserName() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getAccountEq())) {
                sql.WHERE("a.account = '" + evt.getAccountEq() + "'");
            }
            if (StringUtils.isNotBlank(evt.getUserNameEq())) {
                sql.WHERE("a.userName = '" + evt.getUserNameEq() + "'");
            }
            if (StringUtils.isNotBlank(evt.getParentAccount())) {
                sql.WHERE("a.account like '%" + evt.getParentAccount() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getUserName())) {
                sql.WHERE("b.userName like '%" + evt.getUserName() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getParentUserNo())) {
                sql.WHERE("b.parentGameId = '" + evt.getParentUserNo() + "'");
            }
            if (StringUtils.isNotBlank(evt.getAccount())) {
                sql.WHERE("b.account like '%" + evt.getAccount() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "'");
            }
            if (StringUtils.isBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime <= '" + evt.getEndTime() + "'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "' AND b.agentTime <= '" + evt.getEndTime() + "'");
            }
        }
        sql.WHERE("b.status!='D'");
        return sql;
    }


    public String queryCount(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("count(1)").FROM(TBL + " b");
        sql = build(evt, sql);
        return sql.toString();
    }

    public String queryByParam(QueryAgentUserEvt evt) {
        SQL sql = new SQL().SELECT("b.*,\n" +
                "\t(select sum(amount) as totalRate from t_agent_user_rate a where a.agentGameId = b.gameId) as totalRate,\n" +
                "  (select sum(amount) as totalRate from t_agent_user_rate a where a.gameId = b.gameId) as totalOffer")
                .FROM(TBL + " b");
        sql = build(evt, sql);
        sql.ORDER_BY("b.createTime desc");
        return sql.toString();
    }

    private SQL build(QueryAgentUserEvt evt, SQL sql) {
        if (evt.getId() != null) {
            sql.WHERE("b.id = " + evt.getId());
        } else {
            if (StringUtils.isNotBlank(evt.getUserNo())) {
                sql.WHERE("b.userNo = '" + evt.getUserNo() + "'");
            }
            if (evt.getCreateUser() != null) {
                sql.WHERE("createUser = " + evt.getCreateUser());
            }
            if (evt.getAgentLevel() != null) {
                sql.WHERE("b.agentLevel = " + evt.getAgentLevel());
            }
            if (evt.getNotEqAgentLevel() != null) {
                sql.WHERE("b.agentLevel != " + evt.getNotEqAgentLevel());
            }
            if (StringUtils.isNotBlank(evt.getUserName())) {
                sql.WHERE("b.userName like '%" + evt.getUserName() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getGameId())) {
                sql.WHERE("b.gameId = '" + evt.getGameId() + "'");
            }
            if (StringUtils.isNotBlank(evt.getParentUserNo())) {
                sql.WHERE("b.parentGameId = '" + evt.getParentUserNo() + "'");
            }
            if (StringUtils.isNotBlank(evt.getAccount())) {
                sql.WHERE("b.account like '%" + evt.getAccount() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getAccountEq())) {
                sql.WHERE("b.account = '" + evt.getAccountEq() + "'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "'");
            }
            if (StringUtils.isBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime <= '" + evt.getEndTime() + "'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "' AND b.agentTime <= '" + evt.getEndTime() + "'");
            }
        }
        sql.WHERE("b.status!='D'");
        return sql;
    }


    /* 提现申请 */

    public String queryWithDrawCount(QueryWithDrawEvt evt) {
        SQL sql = new SQL().SELECT("count(1)").FROM("t_with_draw a,t_agent_user b");
        sql = buildWithDraw(evt, sql);
        return sql.toString();
    }

    public String queryWithDrawByParam(QueryWithDrawEvt evt) {
        SQL sql = new SQL().SELECT("*,(select sum(amount) as totalRate from t_agent_user_rate c where c.agentGameId = b.gameId) as totalRate,\n" +
                "(select sum(amount) as totalRate from t_agent_user_rate c where c.gameId = b.gameId) as totalOffer").FROM(
                "t_with_draw a,t_agent_user b");
        sql = buildWithDraw(evt, sql);
        sql.ORDER_BY("a.createTime desc");
        return sql.toString();
    }

    private SQL buildWithDraw(QueryWithDrawEvt evt, SQL sql) {
        sql.WHERE("a.userNo = b.userNo");
        if (evt.getId() != null) {
            sql.WHERE("a.id = " + evt.getId());
        } else {
            if (StringUtils.isNotBlank(evt.getUserNo())) {
                sql.WHERE("a.userNo = '" + evt.getUserNo() + "'");
            }
            if (StringUtils.isNotBlank(evt.getUserName())) {
                sql.WHERE("b.userName like '%" + evt.getUserName() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getAccount())) {
                sql.WHERE("b.account like '%" + evt.getAccount() + "%'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "'");
            }
            if (StringUtils.isBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime <= '" + evt.getEndTime() + "'");
            }
            if (StringUtils.isNotBlank(evt.getStartTime()) && StringUtils.isNotBlank(evt.getEndTime())) {
                sql.WHERE("b.agentTime >= '" + evt.getStartTime() + "' AND agentTime <= '" + evt.getEndTime() + "'");
            }
        }
        sql.WHERE("a.status!='D'");
        return sql;
    }


}
